package my.springboot.util;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.expression.Expression;
import org.springframework.expression.common.TemplateParserContext;
import org.springframework.expression.spel.standard.SpelExpressionParser;
import org.springframework.expression.spel.support.StandardEvaluationContext;
import org.springframework.util.StringUtils;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel操作工具类
 * @author fengbo
 * @date 2018/2/1
 */
public class ExcelUtils {

    private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * Excel导出
     * @param is 导出excel的目标输入流
     * @param rootObjectList 导出的数据
     * @param out 导出的输出流
     */
    public static boolean export(InputStream is, List<?> rootObjectList, OutputStream out) {
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(is);

            // 分sheet进行处理
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                XSSFSheet sheet = wb.getSheetAt(i);
                XSSFRow currentRow = sheet.getRow(1);
                if (currentRow == null) {
                    continue;
                }

                Map<Integer, String> map = new HashMap<>(16);
                for (int j = 0; j <= currentRow.getLastCellNum(); j++) {
                    XSSFCell cell = currentRow.getCell(j);
                    if (null == cell) {
                        continue;
                    }
                    String cellValue = cell.getRichStringCellValue().getString().trim();
                    if (StringUtils.isEmpty(cellValue)) {
                        continue;
                    }
                    // 存放<单元格列号, 单元格内容>。单元格内容是除去tag之外的
                    map.put(j, cellValue);
                }

                setMultiData(currentRow, rootObjectList, map);

            }

            wb.write(out);
            out.flush();
            return true;
        } catch (IOException e) {
            log.error("Export Excel Failed" + e.getMessage());
            e.printStackTrace();
        } finally {
            closeResource(is, out, wb);
        }
        return false;
    }

    /**
     * 进行拷贝和赋值
     * @param ls 导出的数据
     */
    private static void setMultiData(XSSFRow currentRow, List<?> ls, Map<Integer, String> map) {

        XSSFSheet sheet = currentRow.getSheet();
        // 行往下移 只有一条数据时，不需要下移
        if(ls.size() > 1){
            sheet.shiftRows(2, sheet.getLastRowNum() + 3, (ls.size() - 1), true, false);
        }

        for (int i = 0; i < ls.size(); i++) {
            Object rootObjectItem = ls.get(i);
            XSSFRow curRow;
            if (i == 0) {
                curRow = currentRow;
            } else {
                curRow = sheet.createRow(i + 1);
                // 拷贝样式
                copyCellStyle(currentRow, curRow);
                // 合并
                copyMergeRegion(sheet, i + 1);
            }

            StandardEvaluationContext context = new StandardEvaluationContext();
            // 处理当前行里面的每个单元格：替换内容
            for (Integer key : map.keySet()) {
                String cellContent = map.get(key) == null ? "" : map.get(key);
                XSSFCell c = curRow.getCell(key);
                c.setCellValue(parseValue(cellContent, rootObjectItem, context));
            }
        }

    }

    /**
     * 拷贝样式
     * @param src 拷贝源
     * @param des 拷贝目的
     */
    private static void copyCellStyle(XSSFRow src, XSSFRow des) {
        for (int i = src.getFirstCellNum(); i < src.getLastCellNum(); i++) {
            des.createCell(i).setCellStyle(src.getCell(i).getCellStyle());
        }
    }

    /**
     * 拷贝合并单元格
     * @param sheet sheet
     * @param desRow 合并的列
     */
    private static void copyMergeRegion(XSSFSheet sheet, int desRow) {
        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
            CellRangeAddress oldRegion = sheet.getMergedRegion(j);
            if ((oldRegion.getFirstRow() == 1) && (oldRegion.getLastRow() == 1)) {
                int oldFirstCol = oldRegion.getFirstColumn();
                int oldLastCol = oldRegion.getLastColumn();
                CellRangeAddress newRegion = new CellRangeAddress(desRow, desRow, oldFirstCol, oldLastCol);
                sheet.addMergedRegion(newRegion);
            }
        }
    }

    private static void closeResource(InputStream is, OutputStream out, XSSFWorkbook wb) {
        try {
            if (is != null) {
                is.close();
            }
            if (out != null) {
                out.close();
            }
            if (wb != null) {
                wb.close();
            }
        } catch (IOException e) {
            log.error("Export Excel Stream Closed Failed" + e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 处理EL表达式
     * @param content 匹配的字符串
     * @param rootObjectItem 匹配的对象
     * @return 匹配完成后的值
     */
    private static String parseValue(String content, Object rootObjectItem, StandardEvaluationContext context) {
        // 处理EL表达式
        Expression expression = new SpelExpressionParser().parseExpression(content, new TemplateParserContext());
        return expression.getValue(context, rootObjectItem, String.class);
    }
}
